package edu.cicese.alz;

import edu.cicese.alz.swing.dialogs.ErrorDialog;
import edu.cicese.alz.util.IncenseUtil;
import edu.cicese.alz.util.Utilities;

import java.awt.Component;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.List;

/**
 * Created by: Eduardo Quintana Contreras
 * Date: 29/05/12
 * Time: 12:03 PM
 */
public class Query {
	public static Connection connection;
	public static Statement statement;
	public static ResultSet resultSet;


	public static String querySubject() {
		return "SELECT USER_ID, NAME, AGE, HOME_LATITUDE, HOME_LONGITUDE FROM participant";
	}

	public static String querySubject(String name) {
		return "SELECT USER_ID, NAME, AGE, HOME_LATITUDE, HOME_LONGITUDE FROM participant WHERE NAME LIKE '%" + name + "%'";
	}

	public static String querySubject(int userID) {
		return "SELECT USER_ID, NAME, AGE, HOME_LATITUDE, HOME_LONGITUDE FROM participant WHERE USER_ID = " + userID;
	}

	public static String querySubjectHome(int userID) {
		return "SELECT HOME_LATITUDE, HOME_LONGITUDE FROM participant WHERE USER_ID = " + userID;
	}

	public static String querySubjectIDs() {
		return "SELECT USER_ID FROM participant";
	}

	public static String queryLastUserID() {
		return "SELECT USER_ID \n" +
				"FROM participant\n" +
				"ORDER BY USER_ID DESC \n" +
				"LIMIT 1";
	}

	public static String queryUserTables(int userID) {
		return "SELECT TABLES FROM participant WHERE USER_ID = " + userID;
	}

	public static String queryAccelerometerCount(int userID, int tableID) {
		return "SELECT COUNT FROM table_limit \n" +
				"WHERE USER_ID = " + userID + " AND \n" +
				"TABLE_ID = " + tableID;
	}

	public static String queryInsertSubject(List<Object[]> subjects) {
		String query = "INSERT INTO participant (USER_ID, NAME, AGE, TABLES, HOME_LATITUDE, HOME_LONGITUDE) \n" +
				"VALUES \n";
		int c = 0;
		for (Object[] subject : subjects) {
			String age = IncenseUtil.replaceWithNull(subject[2].toString());
			String latitude = IncenseUtil.replaceWithNull(subject[3].toString());
			String longitude = IncenseUtil.replaceWithNull(subject[4].toString());
//			if (age.equals("")) {
//				age = "NULL";
//			}
			query += "(" + subject[0] + ", '" + subject[1] + "', " + age + ", " + 0 + ", " + latitude + ", " + longitude + ")";
			if (++c < subjects.size()) {
				query += ",\n";
			} else {
				query += ";";
			}
		}
		return query;
	}

	public static String queryDeleteSubject(List<Integer> subjects) {
		String query = "DELETE FROM participant WHERE USER_ID IN (";
		int c = 0;
		for (Integer subjectID : subjects) {
			query += subjectID;
			if (++c < subjects.size()) {
				query += ", ";
			} else {
				query += ");";
			}
		}
		return query;
	}

	public static String queryDropAccelerometerTables(List<Integer> subjects) {
		String query = "DROP TABLE ";
		int tables;
		boolean deleted = false;

		for (Integer userID : subjects) {
			tables = 0;

			try {
				if (openConnection()) {
					resultSet = statement.executeQuery(queryUserTables(userID));
					if (resultSet.next()) {
						tables = resultSet.getInt(1);
					}

					if (tables != 0) {
						deleted = true;
						query += queryDropAccelerometerTables(userID, tables);
						query += ", ";
					}
				} else {
					System.out.println("Couldn't connect to database.");
				}

			} catch (SQLException e) {
				e.printStackTrace();
			} finally {
				close();
			}
		}
		if (deleted) {
			query = query.substring(0, query.length() - 2) + ";";
		} else {
			query = "";
		}

		return query;
	}

	public static String queryDropAccelerometerTables(int userID, int tables) {
		String ss = "";
		String base = "ACCELEROMETER_" + userID + "_";
		for (int t = 1; t <= tables; t++) {
			ss += base + t;
			if (t < tables) {
				ss += ", ";
			}
		}
		return ss;
	}

	public static String queryUpdateSubject(Object[] subject) {
		return "UPDATE participant \n" +
				"SET NAME = '" + subject[1] + "', " +
				"AGE = " + IncenseUtil.replaceWithNull(subject[2].toString()) + ",\n" +
				"HOME_LATITUDE = " + IncenseUtil.replaceWithNull(subject[3].toString()) + ",\n" +
				"HOME_LONGITUDE = " + IncenseUtil.replaceWithNull(subject[4].toString()) + "\n" +
				"WHERE USER_ID = " + subject[0] + ";";
	}


	public static String queryAccelerometerFirstDate(int userID) {
		return "SELECT FIRST\n" +
				"FROM table_limit\n" +
				"WHERE USER_ID = " + userID + "\n" +
				"ORDER BY FIRST ASC\n" +
				"LIMIT 1;";
	}

	public static String queryAccelerometerLastDate(int userID) {
		return "SELECT LAST\n" +
				"FROM table_limit\n" +
				"WHERE USER_ID = " + userID + "\n" +
				"ORDER BY LAST DESC\n" +
				"LIMIT 1;";
	}

	public static String queryGPSFirstDate(int userID) {
		return queryDate(userID, "gps", "ASC");
	}

	public static String queryGPSLastDate(int userID) {
		return queryDate(userID, "gps", "DESC");
	}

	public static String queryNFCFirstDate(int userID) {
		return queryDate(userID, "nfc", "ASC");
	}

	public static String queryNFCLastDate(int userID) {
		return queryDate(userID, "nfc", "DESC");
	}

	public static String queryWIFIFirstDate(int userID) {
		return queryDate(userID, "wifi", "ASC");
	}

	public static String queryWIFILastDate(int userID) {
		return queryDate(userID, "wifi", "DESC");
	}

	public static String queryDate(int userID, String table, String order) {
		return "SELECT TIMESTAMP\n" +
				"FROM " + table + "\n" +
				"WHERE USER_ID = " + userID + "\n" +
				"ORDER BY TIMESTAMP " + order + "\n" +
				"LIMIT 1;";
	}


	public static String queryCreateAccelerometerTable(int userID, int tableID) {
		return "CREATE TABLE IF NOT EXISTS accelerometer_" + userID + "_" + tableID + " (\n" +
				"ID int(11) NOT NULL AUTO_INCREMENT,\n" +
				"AXIS_X double NOT NULL,\n" +
				"AXIS_Y double NOT NULL,\n" +
				"AXIS_Z double NOT NULL,\n" +
				"TIMESTAMP bigint(14) unsigned NOT NULL,\n" +
				"USER_ID smallint(3) unsigned NOT NULL,\n" +
				"PRIMARY KEY (ID),\n" +
				"KEY TIMESTAMP (TIMESTAMP),\n" +
				"KEY USER_ID (USER_ID)\n" +
				") ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_spanish_ci AUTO_INCREMENT=1;";
	}

	public static String queryUpdateUserTables(int userID, int newTableID) {
		return "UPDATE participant SET TABLES = " + newTableID + " WHERE USER_ID = " + userID;
	}

	public static String querySelectTableLimit(int userID, int tableID) {
		return "SELECT * FROM table_limit \n" +
				"WHERE USER_ID = " + userID + " AND \n" +
				"TABLE_ID = " + tableID;
	}

	public static String queryInsertTableLimit(int userID, int tableID, long timestamp1, long timestamp2, int count) {
		return "INSERT INTO table_limit VALUES (" +
				userID + ", " +
				tableID + ", " +
				timestamp1 + ", " +
				timestamp2 + ", " +
				count + ")";
	}

	public static String queryUpdateTableLimit(int userID, int tableID, long timestamp1, long timestamp2, int count) {
		return "UPDATE table_limit SET FIRST = " + timestamp1 + ", LAST = " + timestamp2 + ", COUNT = " + count + "\n" +
				"WHERE USER_ID = " + userID + " AND\n" +
				"TABLE_ID = " + tableID;
	}


	public static String queryLoadAccelerometerDsv(int userID, int tableID, String dsvFile) {
		return "LOAD DATA INFILE '" + dsvFile.replace("\\", "/") + "'\n" +
				"INTO TABLE accelerometer_" + userID + "_" + tableID + "\n" +
				"FIELDS TERMINATED BY '" + Utilities.getFieldSeparator() + "' LINES TERMINATED BY '\\n'\n" +
				"(AXIS_X, AXIS_Y, AXIS_Z, TIMESTAMP, USER_ID);";
	}

	public static String queryLoadGPSDsv(String dsvFile) {
		return "LOAD DATA INFILE '" + dsvFile.replace("\\", "/") + "' " +
				"INTO TABLE gps FIELDS TERMINATED BY '" + Utilities.getFieldSeparator() + "' LINES TERMINATED BY '\\n';";
	}

	public static String queryLoadNFCDsv(String dsvFile) {
		return "LOAD DATA INFILE '" + dsvFile.replace("\\", "/") + "' " +
				"INTO TABLE nfc FIELDS TERMINATED BY '" + Utilities.getFieldSeparator() + "' LINES TERMINATED BY '\\n';";
	}

	public static String queryLoadWIFIDsv(String dsvFile) {
		return "LOAD DATA INFILE '" + dsvFile.replace("\\", "/") + "' " +
				"INTO TABLE wifi FIELDS TERMINATED BY '" + Utilities.getFieldSeparator() + "' LINES TERMINATED BY '\\n';";
	}


	//Reports
	public static String querySelectTableLimit(int userID, String value, int tableID) {
		return "SELECT " + value + " FROM table_limit WHERE USER_ID = " + userID + " AND TABLE_ID = " + tableID;
	}

	public static String querySelectTableID(int userID, long timestamp) {
		return "SELECT TABLE_ID FROM table_limit\n" +
				"WHERE USER_ID = " + userID + " AND FIRST <= " + timestamp + " AND LAST >= " + timestamp;
	}

	public static String queryGPSData(int userID, long timestamp1, long timestamp2) {
		return "SELECT LATITUDE, LONGITUDE, TIMESTAMP, ALTITUDE, ACCURACY\n" +
				"FROM gps\n" +
				"WHERE USER_ID = " + userID + " AND\n" +
				"TIMESTAMP >= " + timestamp1 + " AND TIMESTAMP <= " + timestamp2;
	}


	public static String queryNFCData() {
		return "SELECT MESSAGE FROM nfc";
	}

	public static String queryNFCData(int userID, long timestamp1, long timestamp2) {
		return "SELECT MESSAGE, TIMESTAMP\n" +
				"FROM nfc\n" +
				"WHERE USER_ID = " + userID + " AND\n" +
				"TIMESTAMP >= " + timestamp1 + " AND TIMESTAMP <= " + timestamp2;
	}


	public static ResultSet executeQuery(String query) {
		if (openConnection()) {
			try {
				return statement.executeQuery(query);
			} catch (SQLException e) {
//				e.printStackTrace();
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static boolean executeUpdate(String query) {
		boolean done;
		if (done = openConnection()) {
			try {
				statement.executeUpdate(query);
			} catch (SQLException e) {
//				close();
				done = false;
			} finally {
				close();
			}
		} else {
			System.out.println("Couldn't connect to database.");
		}
		return done;
	}


	public static ResultSet getSubjects() {
		if (openConnection()) {
			try {
				return statement.executeQuery(Query.querySubject());
			} catch (SQLException e) {
				e.printStackTrace();
//				Query.close(connection, statement, resultSet);
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static ResultSet getSubjects(String name) {
		if (openConnection()) {
			try {
				return statement.executeQuery(Query.querySubject(name));
			} catch (SQLException e) {
				e.printStackTrace();
//				Query.close(connection, statement, resultSet);
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static ResultSet getSubjects(int userID) {
		if (openConnection()) {
			try {
				return statement.executeQuery(Query.querySubject(userID));
			} catch (SQLException e) {
				e.printStackTrace();
//				Query.close(connection, statement, resultSet);
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static ResultSet getUserTables(int userID) {
		if (openConnection()) {
			try {
				return statement.executeQuery(Query.queryUserTables(userID));
			} catch (SQLException e) {
				e.printStackTrace();
//				Query.close(connection, statement, resultSet);
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static ResultSet getAccelerometerCount(int userID, int tableID) {
		if (openConnection()) {
			try {
				return statement.executeQuery(Query.queryAccelerometerCount(userID, tableID));
			} catch (SQLException e) {
				e.printStackTrace();
//				Query.close(connection, statement, resultSet);
				close();
				return null;
			}
		} else {
			System.out.println("Couldn't connect to database.");
			return null;
		}
	}

	public static void createAccelerometerTable(int userID, int tableID) {
		if (openConnection()) {
			try {
				statement.executeUpdate(queryCreateAccelerometerTable(userID, tableID));
				statement.executeUpdate(queryUpdateUserTables(userID, tableID));
			} catch (SQLException e) {
				e.printStackTrace();
//				close();
			} finally {
				close();
			}
		} else {
			System.out.println("Couldn't connect to database.");
		}
	}

	public static void loadAccelerometerDsv(int userID, int tableID, String dsvPath, long timestamp1, long timestamp2, int count, boolean newTableLimit) {
		if (openConnection()) {
			try {
				statement.executeUpdate(queryLoadAccelerometerDsv(userID, tableID, dsvPath));
				if (newTableLimit) {
					statement.executeUpdate(queryInsertTableLimit(userID, tableID, timestamp1, timestamp2, count));
				} else {
					statement.executeUpdate(queryUpdateTableLimit(userID, tableID, timestamp1, timestamp2, count));
				}
			} catch (SQLException e) {
				e.printStackTrace();
//				close();
			} finally {
				close();
			}
		} else {
			System.out.println("Couldn't connect to database.");
		}
	}


	public static boolean openConnection() {
		try {
			// This will load the MySQL driver, each DB has its own driver
			Class.forName("com.mysql.jdbc.Driver");
			// Setup the connection with the DB
			connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/alzheimerdb", "root", "alz");
			// Statements allow to issue SQL queries to the database
			statement = connection.createStatement();
			return true;
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
			return false;
		}
	}

	public static void close() {
		close(connection, statement, resultSet);
	}

	public static void close(Connection conn, Statement stmt, ResultSet rs) {
		try {
			rs.close();
		} catch (Exception e) { /* ignored */ }
		try {
			stmt.close();
		} catch (Exception e) { /* ignored */ }
		try {
			conn.close();
		} catch (Exception e) { /* ignored */ }
	}

	
	public static void showConnectionError(Component owner) {
		new ErrorDialog(owner, "Database error", "Couldn't connect to database.\n[*]Or there was a MYSQL syntax error.");
	}
}
